﻿using System.Data;
using System.Threading.Tasks;
using Dapper;
using Entity;
using IRepository;

namespace Repository
{
    public class ProductInformationRepository : IProductInformationRepository
    {
        private readonly IDbConnection _dbConnection;

        public ProductInformationRepository(IDbConnection dbConnection)
        {
            _dbConnection = dbConnection;
        }
        public async Task<ProductInformationEntity> GetByCodeAsync(string code)
        {
            var sql = "SELECT * FROM wms_basic_product_information WHERE product_code = @Code";
            return await _dbConnection.QueryFirstOrDefaultAsync<ProductInformationEntity>(sql, new { Code = code });
        }
        public async Task<ProductInformationEntity> GetByCodeAsync(string productCode, IDbTransaction transaction)
        {
            var sql = "SELECT * FROM wms_basic_product_information WHERE product_code = @Code AND is_delete = '0'";
            return await _dbConnection.QueryFirstOrDefaultAsync<ProductInformationEntity>(
                sql,
                new { Code = productCode },
                transaction
            );
        }
        // 实现分页搜索
        public async Task<(List<ProductInformationEntity> list, int total)> SearchAsync(
            string keyword, string unit, int pageNum, int pageSize)
        {
            // 构建查询条件
            var conditions = new List<string>();
            var parameters = new DynamicParameters();

            // 基础条件：未删除（假设存在is_delete字段）
            conditions.Add("is_delete = 0");

            // 关键词搜索（编号或名称）
            if (!string.IsNullOrEmpty(keyword))
            {
                conditions.Add("(product_code LIKE @Keyword OR product_name LIKE @Keyword)");
                parameters.Add("Keyword", $"%{keyword}%");
            }

            // 单位筛选
            if (!string.IsNullOrEmpty(unit))
            {
                conditions.Add("unit = @Unit");
                parameters.Add("Unit", unit);
            }

            // 拼接WHERE条件
            var whereSql = conditions.Any() ? $"WHERE {string.Join(" AND ", conditions)}" : "";

            // 总条数查询
            var totalSql = $"SELECT COUNT(1) FROM wms_basic_product_information {whereSql}";
            var total = await _dbConnection.ExecuteScalarAsync<int>(totalSql, parameters);

            // 分页数据查询
            var offset = (pageNum - 1) * pageSize;
            var dataSql = $@"
                SELECT * FROM wms_basic_product_information 
                {whereSql}  
                ORDER BY create_time DESC
                OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

            parameters.Add("Offset", offset);
            parameters.Add("PageSize", pageSize);

            var list = (await _dbConnection.QueryAsync<ProductInformationEntity>(dataSql, parameters)).ToList();

            return (list, total);
        }
        // 带仓库库存的搜索（优化后：合并相同货品编号，返回总库存）
        public async Task<(List<ProductInformationEntity> list, int total)> SearchWithStockAsync(
            string? keyword, int outWarehouseId,
            int pageNum, int pageSize)
        {
            pageNum = Math.Max(pageNum, 1);
            pageSize = Math.Clamp(pageSize, 1, 100);
            if (outWarehouseId <= 0)
                throw new ArgumentException("调出仓库ID必须大于0", nameof(outWarehouseId));

            var conditions = new List<string> { "p.is_delete = 0" }; // 基础条件：产品未删除
            var parameters = new DynamicParameters();
            parameters.Add("OutWarehouseId", outWarehouseId);

            // 关键词搜索（货品编号/名称模糊匹配）
            if (!string.IsNullOrWhiteSpace(keyword))
            {
                conditions.Add("(p.product_code LIKE @Keyword OR p.product_name LIKE @Keyword)");
                parameters.Add("Keyword", $"%{keyword}%");
            }

            var whereSql = conditions.Any() ? $"WHERE {string.Join(" AND ", conditions)}" : "";

            // 1. 总条数查询（按货品编号去重计数）
            var totalSql = $@"
SELECT COUNT(DISTINCT p.product_id) 
FROM wms_basic_product_information p
LEFT JOIN wms_knzy_inventory ow 
    ON p.product_code = ow.product_code 
    AND ow.warehouse_id = @OutWarehouseId 
    AND ow.is_delete = 0  -- 排除已删除的库存记录
{whereSql}";
            var total = await _dbConnection.ExecuteScalarAsync<int>(totalSql, parameters);

            // 2. 分页查询（核心：按货品分组，聚合总库存）
            var offset = (pageNum - 1) * pageSize;
            var dataSql = $@"
SELECT 
    p.product_id,
    p.product_code,
    p.product_name,
    p.specification_model,
    p.unit,
    p.stkInrefp,  -- 入库单价（取产品表的默认单价）
    p.product_type_id,
    p.type_of_goods,
    p.status,
    p.create_time,
    p.update_time,
    -- 聚合库存：相同货品的库存求和，无库存则为0
    ISNULL(SUM(ow.current_inventory), 0) AS out_warehouse_stock
FROM wms_basic_product_information p
LEFT JOIN wms_knzy_inventory ow 
    ON p.product_code = ow.product_code 
    AND ow.warehouse_id = @OutWarehouseId 
    AND ow.is_delete = 0  -- 排除已删除的库存记录
{whereSql}
-- 按货品唯一标识分组（必须包含所有非聚合字段）
GROUP BY 
    p.product_id, p.product_code, p.product_name,
    p.specification_model, p.unit, p.stkInrefp,
    p.product_type_id, p.type_of_goods, p.status,
    p.create_time, p.update_time
-- 过滤无库存的货品（可选，根据业务需求决定是否保留）
HAVING ISNULL(SUM(ow.current_inventory), 0) > 0
ORDER BY p.create_time DESC
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

            parameters.Add("Offset", offset);
            parameters.Add("PageSize", pageSize);

            // 执行查询并转换为实体列表
            var list = (await _dbConnection.QueryAsync<ProductInformationEntity>(dataSql, parameters))
                .ToList();

            return (list, total);
        }

        /// <summary>
        /// 进货明细添加的查询
        /// </summary>
        /// <param name="code">编号</param>
        /// <param name="Unit">单位</param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<List<ProductInformationFind>> GetByCodeAsync(string? code = null, string? Unit = null)
        {
            // 构建带分组汇总的SQL，确保入库单价和库存数据正确关联
            string sql = @"
                SELECT 
                    wbi.Product_ID,
                    wbi.product_code,
                    wbi.product_name,
                    wbi.specification_model,
                    wbi.unit,
                    wbi.stkInrefp AS inboundPrice,  -- 入库单价
                    wbi.type_of_goods AS type_of_goods,
                    ISNULL(SUM(wki.current_inventory), 0) AS currentInventory  -- 汇总当前库存（无库存时为0）
                FROM wms_basic_product_information wbi
                LEFT JOIN wms_knzy_inventory wki 
                    ON wbi.product_code = wki.product_code 
                    AND wki.is_delete = 0  -- 排除已删除的库存记录
                WHERE wbi.is_delete = '0'  -- 排除已删除的产品
                    AND (@Code IS NULL OR wbi.product_code LIKE @Code)
                    AND (@Unit IS NULL OR wbi.unit = @Unit)
                GROUP BY 
                    wbi.product_code,
                    wbi.product_name,
                    wbi.specification_model,
                    wbi.unit,
                    wbi.stkInrefp,
                    wbi.type_of_goods,
                    wbi.Product_ID
                ORDER BY wbi.product_code";

            // 参数化处理查询条件
            var parameters = new DynamicParameters();
            if (!string.IsNullOrEmpty(code))
            {
                parameters.Add("Code", $"%{code}%");
            }
            else
            {
                parameters.Add("Code", null);
            }
            parameters.Add("Unit", Unit);

            return (await _dbConnection.QueryAsync<ProductInformationFind>(sql, parameters)).AsList();
        }
        public async Task<(List<ProductTableItem> list, int total)> MultiConditionSearchAsync(ProductQueryParam param)
        {
            var sql = @"
SELECT 
    p.product_code, p.product_name, 
    CASE p.status WHEN 1 THEN '启用' ELSE '停用' END AS Status,
    t.product_type_name, p.specification_model,
    w.warehouse_name, 
    ISNULL(sl.location_name, '未知库位') AS LocationName,
    i.current_inventory,
    p.stkInrefp * i.current_inventory AS InventoryAmount
FROM wms_basic_product_information p
LEFT JOIN wms_basic_type_of_goods t ON p.product_type_id = t.product_type_id
LEFT JOIN wms_knzy_inventory i ON p.product_code = i.product_code
LEFT JOIN wms_basic_warehouse w ON i.warehouse_id = w.warehouse_id
LEFT JOIN wms_basic_storage_location sl ON i.location = sl.location_name AND i.warehouse_id = sl.warehouse_id
WHERE p.is_delete = 0
";
            var parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(param.Keyword))
            {
                sql += " AND (p.product_name LIKE @Keyword OR p.product_code LIKE @Keyword)";
                parameters.Add("@Keyword", $"%{param.Keyword}%");
            }
            if (param.ProductTypeId.HasValue)
            {
                sql += " AND p.product_type_id = @ProductTypeId";
                parameters.Add("@ProductTypeId", param.ProductTypeId.Value);
            }
            if (!string.IsNullOrWhiteSpace(param.WarehouseName))
            {
                sql += " AND w.warehouse_name LIKE @WarehouseName";
                parameters.Add("@WarehouseName", $"%{param.WarehouseName}%");
            }

            // 分页处理
            var totalSql = $"SELECT COUNT(1) FROM ({sql}) AS temp";
            var total = await _dbConnection.ExecuteScalarAsync<int>(totalSql, parameters);
            sql += " ORDER BY p.Product_ID OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";
            parameters.Add("@Offset", (param.PageNum - 1) * param.PageSize);
            parameters.Add("@PageSize", param.PageSize);

            var list = (await _dbConnection.QueryAsync<ProductTableItem>(sql, parameters)).ToList();
            return (list, total);
        }
    }
}